# -*- coding: UTF-8 -*-
import pymysql
from jsonpath import jsonpath
import requests


class DataBase(object):
	different_key = []
	different_value = []
	different_dict = {}

	def __init__(self, host, port, user, password, database):
		# 建立连接通道，建立连接填入（连接数据库的IP地址，端口号，用户名，密码，要操作的数据库，字符编码）
		self.conn = pymysql.connect(
			host=host,
			port=int(port),  # 注意这里得把port转换成int！
			user=user,
			password=password,
			database=database,
			charset="utf8"
		)

		# 创建游标，操作设置为字典类型，返回结果为字典格式！不写默认是元组格式！
		self.cursor = self.conn.cursor(cursor=pymysql.cursors.DictCursor)

	def query(self, sql_string):
		r = self.cursor.execute(sql_string)
		req = self.cursor.fetchall()  # 接收返回的所有数据
		query_data = {'total': r, 'data': req}
		self.conn.commit()  # 提交
		# 操作完成之后，就需要关闭连接
		self.cursor.close()  # 关闭游标
		self.conn.close()  # 关闭连接
		return query_data

	def compare_dict(self, response, result,is_java):
		# 判断字典response的key的值是否与result字典key的值相一致
		is_equal = True
		print(response)

		for key in result.keys():
			if is_java == '1':
				if str(response[self.underline_to_hump(key)]) != str(result[key]):
					is_equal = False
					self.different_dict.update({key: response[key]})
			else:
				if str(response[key]) != str(result[key]):
					is_equal = False
					self.different_dict.update({key: response[key]})
		return is_equal

	def compare_dict_raise(self, response, result):
		# 判断字典response的key的值是否与result字典key的值相一致
		is_equal = True
		for key in result.keys():
			if response[self.hump_to_underline(key)] != result[key]:
				raise Exception("接口返回和数据库字段值不一致!", key, response[self.hump_to_underline(key)], result[key])
				is_equal = False
				break
		return is_equal

	def ergodic_list(self, response, result,is_java):
		# 判断字典response是否与result列表中的某个字典相匹配
		is_equal = False
		for item in result:
			if self.compare_dict(response, item,is_java):
				is_equal = True
				print('返回结果{0} 与 数据库中的 {1} 一致'.format(response, item))
				# break
		return is_equal

	def ergodic_list_raise(self, response, result):
		# 判断字典response是否与result列表中的某个字典相匹配
		is_equal = False
		for item in result:
			if self.compare_dict_raise(response, item):
				is_equal = True
				print('返回结果{0} 与 数据库中的 {1} 一致'.format(response, item))
				# break
		return is_equal

	def ergodic_dict(self, response, result,is_java):
		# 判断两个列表中存放的字典中的key值是否一致
		is_equal = True
		print("response{0}".format(response))
		for i in response:
			print("item{0}".format(i))
			if not self.ergodic_list(i, result,is_java):
				is_equal = False
				print('返回结果{0} 与 数据库数据不一致'.format(i))
				# break
		return is_equal

	def check_list_result(self,response, result):
		if not self.ergodic_list(response, result):
			raise Exception('返回结果与数据库不一致')

	def check_result(self, response, sql_string, is_java):
		"""
		接口返回的response格式要求[{},{},{}]
		:param response：接口返回值
		:param sql_string：sql语句
		"""

		print('****************sql****************语句为：', sql_string)
		result = self.query(sql_string)
		s = "数据库返回结果"
		print(s.center(50, '*'), result['data'])
		if (response!=[] and result['data']!=()) and (response is not None and result['data']!=()):
			if not self.ergodic_dict(response, result['data'], is_java):
				a="接口返回总数为: {0} ;".format(len(response)), "数据库返回总数为: {0} ".format(result['total'])
				# print("接口返回总数为: {0} ;".format(len(response)), "数据库返回总数为: {0} ".format(result['total']))
				return "对比失败，接口返回和数据库不一致,接口不同的为{0};{1}".format(self.different_dict, a)
				# raise Exception('对比失败，接口返回和数据库不一致')
			else:
				print(True)
				a = "接口返回总数为: {0} ;".format(len(response)), "数据库返回总数为: {0} ".format(result['total'])
				return "对比成功，接口返回和数据库一致{0}".format(a)
		elif (response==[] or response is None) and (result['data']==()):
			return "对比成功，接口返回和数据库都为空"
		else:
			return "对比失败，接口返回和数据库不一致,接口返回为{0};数据库返回为{1}".format(response,result['data'])

	def get_response(self):
		url = "http://192.168.31.46:7000/api/user/permission/list"
		r = requests.get(url, headers={'Authorization': 'eyJhbGciOiJIUzI1NiJ9.eyJqdGkiOiJiYTViYTQ5Yi1hNGQ2LTQ3NGItOGZhNC0xN2UyODU2YjAzNDAiLCJzdWIiOiIxNTc1NTM4MzE3MyIsImlzcyI6ImFkbWluIiwiaWF0IjoxNTk3NDk4NTE1LCJleHAiOjE1OTc1MDIxMTV9.yIcRWinlznsRu4rd1wIhJFWrvw3VK2EiGf1VKGALRuQ'}
)
		return r.json()

if __name__ == '__main__':
	sql = 'SELECT id,rights,description FROM bridge_permission'
	c = DataBase("172.23.32.134", 3306, "qlxmdb", "owi3ble2Ez", "test")
	# print(c.check_single_result(w,sql,'1'))
	# res = DataBase().get_response()
	print(c.query(sql))
	# print(len(c.get_response()))
	print(c.get_response())
	print(jsonpath(c.get_response(), '$.data..*'))

